
package com.feib.soeasy.dao.hibernate;

import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.FetchMode;
import org.hibernate.Query;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.stereotype.Repository;

import com.feib.soeasy.dao.GroupDao;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.model.RealAccount;
import com.feib.soeasy.pagination.PaginatedList;


/**
 * @title (#)GroupHibernateDao.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2010/11/19 create by Jimmy Liu<br>
 */
@Repository("groupDao")
public class GroupHibernateDao extends BaseHibernateDao<Group, Long> implements GroupDao {

    /**
     * 依據查詢物件查詢帳單業者
     * @param payment 查詢條件
     * @param paginationConfig 換頁、排序條件
     * @return 查詢結果物件
     */
    @Override
    public PaginatedList<Group> queryPaginationByGroup(Group group, PaginatedList<Group> paginationConfig) {
        return super.queryPaginationByEnity(group, paginationConfig);
    }
    
    protected DetachedCriteria queryDetachedCriteriaByGroup(Group group)
    {
        DetachedCriteria criteria = DetachedCriteria.forClass(this.getPersistentClass());
        criteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);

        if (StringUtils.isNotEmpty(group.getGroupNo()))
            criteria.add(Restrictions.like("groupNo", group.getGroupNo(), MatchMode.START));
        if (StringUtils.isNotEmpty(group.getGroupName()))
            criteria.add(Restrictions.like("groupName", group.getGroupName(), MatchMode.ANYWHERE));

        return criteria;
    } 

    @SuppressWarnings("unchecked")
    public PaginatedList<Group> queryPaginationByGroupExcludeBank(Group group, PaginatedList<Group> paginationConfig) {
        
        PaginatedList<Group> result = new PaginatedList<Group>();
        result.setPageSize(paginationConfig.getPageSize());
        result.setPageNumber(paginationConfig.getPageNumber());
        result.setSortCriterion(paginationConfig.getSortCriterion());
        result.setSortDirection(paginationConfig.getSortDirection());
        
        DetachedCriteria criteria = queryDetachedCriteriaByGroup(group);
        criteria.add(Restrictions.not(Restrictions.eq("groupType", Group.GROUP_TYPE_BANK)));
        if(group.getGroupType()!=null){
        	criteria.add(Restrictions.eq("groupType", group.getGroupType()));
        }
        result.setFullListSize(countByDetachedCriteria(criteria));


        // 筆數大於0
        if (0 < result.getFullListSize())
        {
            criteria = queryDetachedCriteriaByGroup(group);
            criteria.add(Restrictions.not(Restrictions.eq("groupType", Group.GROUP_TYPE_BANK)));
            if(group.getGroupType()!=null){
            	criteria.add(Restrictions.eq("groupType", group.getGroupType()));
            }
            //排序
            if (null != paginationConfig.getSortCriterion())
            {
                // 由小到大
                if (PaginatedList.SORT_DIRECTION_ASC == paginationConfig.getSortDirection())
                {
                    criteria.addOrder(Order.asc(paginationConfig.getSortCriterion()));
                }
                else // 由大到小
                {
                    criteria.addOrder(Order.asc(paginationConfig.getSortCriterion()));
                }
            }


            int maxResults = result.getPageSize(); //the maximum number of results
            int firstResult = result.getFirstIndexForCurrentPage();  //the first result to retrieve, numbered from 0
            result.setPaginatedList(this.getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults));
        }

        return result;
    }
    
    /**
     * 依據業者代號(unique key)取得帳單業者物件
     * @param groupNo 業者代號
     * @return 帳單業者物件
     */
    public Group getGroupByGroupNo(String groupNo)
    {
        Group group = new Group();
        group.setGroupNo(groupNo);
        List<Group> groups = this.findByExample(group, false, false, false);
        return DataAccessUtils.singleResult(groups);
    }
    
    @SuppressWarnings("unchecked")
    public PaginatedList<Group> queryPaginationByGroupvirtualAccountNo(Group group, String accountNo, PaginatedList<Group> paginationConfig)
    {

        PaginatedList<Group> result = new PaginatedList<Group>();

        result.setPageSize(paginationConfig.getPageSize());
        result.setPageNumber(paginationConfig.getPageNumber());
        result.setSortCriterion(paginationConfig.getSortCriterion());
        result.setSortDirection(paginationConfig.getSortDirection());

        result.setFullListSize(super.countByDetachedCriteria(
                        createDetachedCriteriaByByGroupAccountNo(group, accountNo)));


        // 筆數大於0
        if (0 < result.getFullListSize())
        {
            DetachedCriteria criteria = 
                createDetachedCriteriaByByGroupAccountNo(group, accountNo);

            //排序
            if (null != paginationConfig.getSortCriterion())
            {
                // 由小到大
                if (PaginatedList.SORT_DIRECTION_ASC == paginationConfig.getSortDirection())
                {
                    criteria.addOrder(Order.asc(paginationConfig.getSortCriterion()));
                }
                else // 由大到小
                {
                    criteria.addOrder(Order.asc(paginationConfig.getSortCriterion()));
                }
            }


            int maxResults = result.getPageSize(); //the maximum number of results
            int firstResult = result.getFirstIndexForCurrentPage();  //the first result to retrieve, numbered from 0
            result.setPaginatedList(this.getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults));
        }

        return result;
        
    }
    
    protected DetachedCriteria createDetachedCriteriaByByGroupAccountNo(Group group, String accountNo)
    {
        DetachedCriteria criteria = null;
        if (StringUtils.isEmpty(accountNo))
        {
            criteria = DetachedCriteria.forClass(this.getPersistentClass());
            criteria.add(Restrictions.not(Restrictions.eq("groupType", Group.GROUP_TYPE_BANK)));
            Example example = Example.create(group);
            example.enableLike(MatchMode.START);
            example.excludeZeroes();
            criteria.add(example);            
        }
        else
        {
            criteria = DetachedCriteria.forClass(RealAccount.class);            
            criteria.setProjection(Projections.distinct(Projections.property("group")));
            criteria.setFetchMode("group", FetchMode.JOIN);
            
            RealAccount entity = new RealAccount();
            entity.setAccountNo(accountNo);
            Example example = Example.create(entity);
            example.enableLike(MatchMode.START);
            example.excludeZeroes();
            criteria.add(example);
            
            DetachedCriteria detachedCriteria = criteria.createCriteria("group");
            detachedCriteria.add(Restrictions.not(Restrictions.eq("groupType", Group.GROUP_TYPE_BANK)));
            
            example = Example.create(group);
            example.enableLike(MatchMode.START);
            example.excludeZeroes();
            detachedCriteria.add(example);
            
        }
        criteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);
        return criteria;
    }


    /* (non-Javadoc)
     * @see com.feib.soeasy.dao.GroupDao#findByGroupNo(java.lang.String)
     */
    @Override
    public Group findByGroupNo(String groupNo) {
        if( StringUtils.isEmpty(groupNo))
            return null;
        
        DetachedCriteria criteria = DetachedCriteria.forClass(this.getPersistentClass());
        criteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);

        criteria.add(Restrictions.eq("groupNo", groupNo));

        @SuppressWarnings("unchecked")
        List<Group> groups = this.getHibernateTemplate().findByCriteria(criteria);

        return DataAccessUtils.singleResult(groups);
    }
    
    
    
    @Override
    public void updateAmount(Group group)
    {
    	StringBuffer hql = new StringBuffer();
    	hql.append("update Group g ");
    	hql.append("set g.atmAmount = :atmAmount ");
    	hql.append(", g.bankAmount = :bankAmount ");
    	hql.append(", g.chainstoreAmount = :chainstoreAmount ");
    	hql.append(", g.otherAmount = :otherAmount ");
    	hql.append(", g.remittanceAmount = :remittanceAmount ");
    	hql.append(", g.postAmount = :postAmount ");
    	hql.append(", g.selfAmount = :selfAmount ");
    	hql.append("where g.groupPk = :groupPk ");
    	
    	Query query = this.getSession().createQuery(hql.toString());
    	
    	query.setParameter("postAmount",group.getPostAmount());
    	query.setParameter("remittanceAmount",group.getRemittanceAmount());
    	query.setParameter("otherAmount",group.getOtherAmount());
    	query.setParameter("chainstoreAmount",group.getChainstoreAmount());
    	query.setParameter("bankAmount",group.getBankAmount());
    	query.setParameter("atmAmount",group.getAtmAmount());
    	query.setParameter("groupPk", group.getGroupPk());
    	query.setParameter("selfAmount",group.getSelfAmount());
    	
    	query.executeUpdate();
    	
    }
    
    @Override
    public void updateLevel(Group group)
    {
    	StringBuffer hql = new StringBuffer();
    	hql.append("update Group g ");
    	hql.append("set g.postLevel1 = :postLevel1 ");
    	hql.append(", g.postLevel2 = :postLevel2 ");
    	hql.append(", g.postLevel3 = :postLevel3 ");
    	hql.append("where g.groupPk = :groupPk ");
    	
    	Query query = this.getSession().createQuery(hql.toString());
    	
    	query.setParameter("postLevel1",group.getPostLevel1());
    	query.setParameter("postLevel2",group.getPostLevel2());
    	query.setParameter("postLevel3",group.getPostLevel3());
    	
    	query.setParameter("groupPk", group.getGroupPk());
    	
    	query.executeUpdate();
    	
    }

}
